using Eno.Domain.Entity.App;
using Eno.Domain.Repositories;
using Dapper;
using Npgsql;
using System.Data;

namespace Eno.Infrastructure.InfRepositories;

public class UserRepository : IUserRepository<AppUser>
{
    public readonly string _connectionString;  //* 拿的是连接postgresql的配置文件字符串

    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    public async Task<int> AdditionUserAsync(AppUser entity)
    {
        entity.ID = Guid.NewGuid();
        using var conn = new NpgsqlConnection(_connectionString);
        var number = await conn.ExecuteAsync("insert into app_users(\"ID\",\"Account\",\"Password\",\"Salt\",\"IsActived\",\"IsDeleted\",\"CreatedAt\",\"UpdatedAt\",\"DepartmentID\",\"Remarks\",\"DisplayName\",\"Email\",\"Phone\") values (@ID,@Account,@Password,@Salt,@IsActived,@IsDeleted,@CreatedAt,@UpdatedAt,@DepartmentId,@Remarks,@DisplayName,@Email,@Phone)", entity);
        return number;
    }

    public async Task<int> DeleteUserAsync(Guid userId)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        await conn.OpenAsync();

        using var transaction = await conn.BeginTransactionAsync();
        // 先检查记录是否存在且未删除
        var checkSql = @"SELECT COUNT(*) FROM app_users WHERE ""ID"" = @ID AND ""IsDeleted"" = false";
        var exists = await conn.QuerySingleAsync<int>(checkSql, new { ID = userId }, transaction);

        if (exists == 0)
        {
            await transaction.RollbackAsync();
            return 0; // 记录不存在或已删除
        }

        // 执行删除
        var sql = @"DELETE FROM app_users WHERE ""ID"" = @ID AND ""IsDeleted"" = false";
        var number = await conn.ExecuteAsync(sql, new { ID = userId }, transaction);

        await transaction.CommitAsync();
        await conn.CloseAsync();
        return number;
    }

    public async Task<IEnumerable<AppUser>> GetUserAllAsync()
    {
        using var conn = new NpgsqlConnection(_connectionString);
        return await conn.QueryAsync<AppUser>("select * from app_users");
    }

    public async Task<AppUser?> GetUserByAccount(string account)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        return await conn.QueryFirstOrDefaultAsync<AppUser>("select * from app_users where \"Account\"=@Account", new { Account = account });
    }

    public async Task<AppUser?> GetUserByIdAsync(Guid userId)
    {
        using var conn = new NpgsqlConnection(_connectionString); //! 获得的是一个连接对象
        return await conn.QueryFirstOrDefaultAsync<AppUser>("select * from app_users where \"ID\"=@ID", new { ID = userId });
    }

    public async Task<int> UpdateUserAsync(AppUser entity)
    {
        Console.WriteLine($"[UpdateUserAsync] entity.ID={entity.ID}, entity.Account={entity.Account}, DisplayName={entity.DisplayName}, Email={entity.Email}, Phone={entity.Phone}");
        using var conn = new NpgsqlConnection(_connectionString);
        var number = await conn.ExecuteAsync(
            "update app_users set \"Account\"=@Account, \"Password\"=@Password, \"Salt\"=@Salt, \"DepartmentID\"=@DepartmentID, \"IsActived\"=@IsActived, \"IsDeleted\"=@IsDeleted, \"Remarks\"=@Remarks, \"UpdatedAt\"=@UpdatedAt, \"DisplayName\"=@DisplayName, \"Email\"=@Email, \"Phone\"=@Phone, \"Avatar\"=@Avatar where \"ID\"=@ID",
            entity);
        Console.WriteLine($"[UpdateUserAsync] SQL affected rows: {number}");
        return number;
    }
    // 获取用户角色列表
    public async Task<List<string>> GetUserRolesAsync(Guid userId)
    {
        using var conn = new NpgsqlConnection(_connectionString);
        var sql = @"
            SELECT r.""RoleName""
            FROM app_users_roles ur
            JOIN app_roles r ON ur.""RolesID"" = r.""ID""
            WHERE ur.""UsersID"" = @UserID
            ";
        var roles = await conn.QueryAsync<string>(sql, new { UserID = userId });
        return roles.ToList();
    }



}